package com.lyf.dao.impl;

import com.lyf.dao.EmployeeDao;
import com.lyf.dao.JobDao;
import com.lyf.pojo.Dept;
import com.lyf.pojo.Job;
import com.lyf.utils.DBTools;
import com.lyf.utils.PageModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JobDaoImpl implements JobDao {
    QueryRunner qr = DBTools.getQueryRunner();
    EmployeeDao employeeDao = new EmployeeDaoImpl();
    @Override
    public void add(Job job) {
        String sql = "insert into job_inf(name,remark) values (?,?)";
        try {
            qr.update(sql,job.getName(),job.getRemark());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void delete(Integer id) {
        String sql = "update job_inf set isdelete = 1 where id = ?";
//        QueryRunner qr = DBTools.getQueryRunner();
        try {
            qr.update(sql,id);
            //同时删除职位关联的员工
            employeeDao.deleteByJobId(id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void update(Job job) {
        String sql = "update job_inf set name = ? , remark = ? where id = ?";
//        QueryRunner qr = DBTools.getQueryRunner();
        try {
            qr.update(sql,job.getName(),job.getRemark(),job.getId());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<Job> selectAll() {
//        QueryRunner qr = DBTools.getQueryRunner();
        ResultSetHandler<List<Job>> rsh = new BeanListHandler<>(Job.class);
        String sql = "select * from job_inf where isdelete = 0";
        List<Job> jobList = null;
        try {
            jobList = qr.query(sql, rsh);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return jobList;
    }

    @Override
    public Job selectById(Integer id) {
//        QueryRunner qr = DBTools.getQueryRunner();
        String sql = "select * from job_inf where id = ?";
        ResultSetHandler<Job> rsh = new BeanHandler<>(Job.class);
        Job job = null;
        try {
            job = qr.query(sql,rsh,id);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                qr.getDataSource().getConnection().close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return job;
    }

    @Override
    public List<Job> queryJob(Job job, PageModel model) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT * FROM job_inf WHERE 1=1");
        List<Object> params = new ArrayList<>(); // 用于存放参数的列表

        // 根据 user 对象的属性添加查询条件
        sql.append(" AND isdelete = 0");
        if (job != null && job.getName() != null && !job.getName().isEmpty()) {
            sql.append(" AND name LIKE ?");
            params.add("%" + job.getName() + "%");
        }
        // 如果有其他条件，以同样的方式添加到 sql 和 params

        // 添加分页的 LIMIT 和 OFFSET
        int pageSize = model.getPageSize();
        int startRow = model.getStartRow();
        sql.append(" LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add(startRow);

        // 执行查询
        try {
            List<Job> jobs = qr.query(sql.toString(), new BeanListHandler<>(Job.class), params.toArray());

            return jobs;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    @Override
    public int getTotalJobCount(Job job) {
//        QueryRunner qr = DBTools.getQueryRunner();
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM job_inf WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 根据 user 对象的属性决定是否添加查询条件
        sql.append(" AND isdelete = 0");
        if (job != null && job.getName() != null && !job.getName().isEmpty()) {
            sql.append(" AND name LIKE ?");
            params.add("%" + job.getName() + "%");
        }
        // 执行查询
        ResultSetHandler<Long> countHandler = new ScalarHandler<>();
        try{
            Long count = qr.query(sql.toString(), countHandler, params.toArray());

            return count.intValue();
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }finally {
            try{
                qr.getDataSource().getConnection().close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}
